<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>query</title>
<style type="text/css">
<style type="text/css">
<!--
.style1 {color: #990033}
.style4 {color: #FF9900}
.style5 {color: #660000}
body {
	background-image: url();
	background-repeat: no-repeat;
}
.style6 {color: #FFFFFF}
-->
</style>
</style>
</head>

<body>

<div align="left">
  <table width="878" height="378" border="0">
    <tr>
      <td height="43" colspan="2" bgcolor="#660000"><h1 align="center" class="style4">Welcome to our SQL testing webpage</h1>
          <h3 align="center"><span class="style4">You can test your queries on our Movie database </span></h3></td>
    </tr>
    <tr>
      <td width="390" height="192" bgcolor="#FFFFCC"><p align="left" class="style1"><span class="style5"><strong><u>Our database has the following tables: </u></strong><br />
          Movie(id, title, year, rating, company)<br />
          Actor(id, last, first, sex, dob, dod)<br />
          Director(id, last, first, dob, dod)<br />
          MovieGenre(mid, genre)<br />
          MovieDirector(mid, did)<br />
          MovieActor(mid, aid, role)<br />
          Review(name, time, mid, rating, comment)<br />
          MaxPersonID(id)<br />
          MaxMovieID(id)</span><br />
      </p></td>
      <td width="478" height="192" bgcolor="#FFFFCC"><form action="query.php" method="POST">
          <textarea name="query" cols="60" rows="8"></textarea>
          <br />
          <input name="submit" type="submit" value="Submit" />
      </form></td>
    </tr>
    <tr>
      <td height="21"><div align="right" class="style5">
        <div align="left"><em><span class="style5"><strong>The resulting query will appear bellow this line ! </strong></span></em></div>
      <div align="left" class="style5"></div></td>
      <td></td>
    </tr>
    <tr>
      <td height="52" colspan="2">
        <?php
	  
          $db_connection = mysql_connect("localhost", "cs143", "");
	  if (!$db_connection) {
    	  	die('Could not connect to MySQL server: ' . mysql_error());
	  }
	  mysql_select_db("CS143", $db_connection);
	  
          $query = $_POST["query"];
	  if ($query == '') {
		echo 'Created by Vahe Musinyan and Arman Abrahamyan';
		exit;
	  }
          else {
	  	$parse = explode(" ",$query);
	  	$word = strtolower($parse[0]);
	  }
	  if ($word!=='select' && $word!=='show') {
		echo 'Sorry, only SELECT and SHOW queries are allowed';
		exit;								  	  }	

          $rs = mysql_query($query, $db_connection);
	  if (!$rs) echo 'Enter a new query: ' . mysql_error();
	  else {
		$num_col = mysql_num_fields($rs); 
		print "<table border=1 cellspacing=1 cellpadding=2><tr align=center>";
		for ($j=0; $j<$num_col; $j++) {
			$name = mysql_field_name($rs, $j);
			print "<td bgcolor=#660000><b><span class=style6>$name</span></b></td>";
			}
		print "</tr>"; 
		
	  	while ($row = mysql_fetch_row($rs)) {
			print "<tr align=center>";
			for ($i=0; $i<$num_col; $i++) {
				$tuple = $row[$i];
				if ($tuple==NULL) $tuple="N/A";
				print "<td bgcolor=#FF9900>$tuple</td>";
			}
			print "</tr>";						
		}
		print "</table>";
	  }
          mysql_close($db_connection);	

        ?>
         <div align="left"></div>
        <div align="left"></div>
        <div align="left"></div>
      <div align="left"></div></td>
    </tr>
    <tr>
      <td height="21" colspan="2"><div align="left"><strong><em>Created by Vahe Musinyan and Arman Abrahamyan</em></strong></div>
      </td>
    </tr>
</table>
</body>

</html>

